# 学生管理模块功能接口
from flask import Blueprint, request, jsonify
from flask_jwt_extended import jwt_required, get_jwt_identity, get_jwt
import exts
import datetime

students_blueprint = Blueprint('students', __name__)


# 新增学生
@students_blueprint.route('/add', methods=['POST'])
@jwt_required()
@exts.permission_required('students', 'C')
def add_student():
    data = {
        'student_id': request.json.get('student_id', None),
        'student_name': request.json.get('student_name', None),
        'school_id': request.json.get('school_id', None),
        'student_grade': request.json.get('student_grade', None),
        'grade': request.json.get('grade', None),
        'class': request.json.get('class', None),
        'subject_selection': request.json.get('subject_selection', None),
        'year': request.json.get('year', None),
        'arrival_classtime': request.json.get('arrival_classtime', None),
        'father_name': request.json.get('father_name', None),
        'father_phone': request.json.get('father_phone', None),
        'mother_name': request.json.get('mother_name', None),
        'mother_phone': request.json.get('mother_phone', None),
        'address': request.json.get('address', None),
        'enrollment_date': request.json.get('enrollment_date', None),
        'status': request.json.get('status', None)
    }
    if (not data['student_id'] or not data['student_name'] or not data['school_id'] or not data['student_grade']
            or not data['grade'] or not data['class'] or not data['subject_selection'] or not data['year']):
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '参数不完整, student_id, student_name, school_id, student_grade, grade, class, '
                       'subject_selection, year 为必填项且不能为空'
        })
    # 若 school_id 为空
    if not data['school_id']:
        data['school_id'] = get_jwt()['school_id']
    # 若 enrollment_date 为空
    if not data['enrollment_date']:
        data['enrollment_date'] = datetime.datetime.now().strftime('%Y-%m-%d')
    # 若 status 为空
    if not data['status']:
        data['status'] = '在读'
    # 将 school_id, student_id 转为 int 类型
    data['school_id'] = int(data['school_id'])
    data['student_id'] = int(data['student_id'])

    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        # 查询学生是否已存在
        cur.execute('SELECT * FROM CurrentStudent WHERE student_id = ?', (data['student_id'],))
        student = cur.fetchone()
        if student:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '学生已存在',
                'detail': '学号 %s 的学生已存在' % data['student_id']
            })
        # 新增学生
        sql = 'INSERT INTO CurrentStudent (student_id, student_name, school_id, student_grade, grade, class, ' \
              'subject_selection, year, arrival_classtime, father_name, father_phone, mother_name, mother_phone, ' \
              'address, enrollment_date, status) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
        cur.execute(sql, (data['student_id'], data['student_name'], data['school_id'], data['student_grade'],
                          data['grade'], data['class'], data['subject_selection'], data['year'],
                          data['arrival_classtime'],
                          data['father_name'], data['father_phone'], data['mother_name'], data['mother_phone'],
                          data['address'], data['enrollment_date'], data['status']))
        # 新增学生历史变动信息
        change_type = '人员变动'
        change_method = '手动变更'
        change_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        remark = '页面手动新增学生信息'
        previous_value = {}
        new_value = {}
        temp = "字段变动 "
        for key, value in data.items():
            if value is not None:
                previous_value[key] = None
                new_value[key] = value
                temp += f"'{key}' : '' -> '{value}'; "
        change_detail = f"""{change_type}, {temp}"""
        previous_value = str(previous_value)
        new_value = str(new_value)
        sql1 = 'INSERT INTO StudentHistoryChange (student_id, school_id, change_type, change_method, change_date, ' \
               'change_detail, previous_value, new_value, remark) VALUES (?,?,?,?,?,?,?,?,?)'
        cur.execute(sql1, (data['student_id'], data['school_id'], change_type, change_method, change_date,
                           change_detail, previous_value, new_value, remark))
        cur.connection.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '新增学生成功',
            'data': {
                'student_id': data['student_id']
            }
        })
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': '服务器内部错误',
            'detail': f'错误详情: {str(e)}'
        })
    finally:
        if conn:
            conn.close()


# 更新学生信息
@students_blueprint.route('/update/<int:student_id>', methods=['PUT'])
@jwt_required()
@exts.permission_required('students', 'U')
def update_student(student_id):
    data = {
        'id': request.json.get('id', None),
        'student_name': request.json.get('student_name', None),
        'school_id': request.json.get('school_id', None),
        'student_grade': request.json.get('student_grade', None),
        'grade': request.json.get('grade', None),
        'class': request.json.get('class', None),
        'subject_selection': request.json.get('subject_selection', None),
        'year': request.json.get('year', None),
        'arrival_classtime': request.json.get('arrival_classtime', None),
        'father_name': request.json.get('father_name', None),
        'father_phone': request.json.get('father_phone', None),
        'mother_name': request.json.get('mother_name', None),
        'mother_phone': request.json.get('mother_phone', None),
        'address': request.json.get('address', None),
        'enrollment_date': request.json.get('enrollment_date', None),
        'status': request.json.get('status', None)
    }
    if (not data['student_name'] or not data['school_id'] or not data['student_grade'] or not data['grade']
            or not data['class'] or not data['subject_selection'] or not data['year']):
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '参数不完整, student_name, school_id, student_grade, grade, '
                       'class, subject_selection, year 为必填项且不能为空'
        })

    # 将 school_id, student_id, id_ 转为 int 类型
    data['school_id'] = int(data['school_id'])
    data['id'] = int(data['id']) if data['id'] else None
    student_id = int(student_id)
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        # 查询学生或学生历史记录是否存在
        if data['id']:
            cur.execute('SELECT * FROM AllStudentRecord WHERE id = ? AND student_id = ?', (data['id'], student_id))
        else:
            cur.execute('SELECT * FROM CurrentStudent WHERE student_id = ?', (student_id,))
        student = cur.fetchone()
        if not student:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '学生不存在',
                'detail': '学号 %s 的学生不存在' % student_id
            })
        # 更新学生信息
        if data['id']:
            sql = '''UPDATE AllStudentRecord SET student_name = ?, school_id = ?, student_grade = ?, grade = ?, class = ?,
                     subject_selection = ?, year = ?, arrival_classtime = ?, father_name = ?, father_phone = ?,
                     mother_name = ?, mother_phone = ?, address = ?, enrollment_date = ?, status = ? WHERE id = ? 
                     AND student_id = ?'''
            cur.execute(sql,
                        (data['student_name'], data['school_id'], data['student_grade'], data['grade'], data['class'],
                         data['subject_selection'], data['year'], data['arrival_classtime'], data['father_name'],
                         data['father_phone'], data['mother_name'], data['mother_phone'], data['address'],
                         data['enrollment_date'], data['status'], data['id'], student_id))
        else:
            sql = '''UPDATE CurrentStudent SET student_name = ?, school_id = ?, student_grade = ?, grade = ?, class = ?,
                     subject_selection = ?, year = ?, arrival_classtime = ?, father_name = ?, father_phone = ?,
                     mother_name = ?, mother_phone = ?, address = ?, enrollment_date = ?, status = ? WHERE student_id = ?'''
            cur.execute(sql,
                        (data['student_name'], data['school_id'], data['student_grade'], data['grade'], data['class'],
                         data['subject_selection'], data['year'], data['arrival_classtime'], data['father_name'],
                         data['father_phone'], data['mother_name'], data['mother_phone'], data['address'],
                         data['enrollment_date'], data['status'], student_id))
        # 新增学生历史变动信息
        change_type = '人员变动'
        change_method = '手动变更'
        change_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        remark = '页面手动更新学生信息'
        previous_value = {}
        new_value = {}
        temp = "字段变动 "
        data.pop('id')
        for key, value in data.items():
            if value != student[key]:
                previous_value[key] = student[key]
                new_value[key] = value
                temp += f"'{key}' : '{student[key]}' -> '{value}'; "
        change_detail = f"""{change_type}, {temp}"""
        previous_value = str(previous_value)
        new_value = str(new_value)
        sql1 = '''INSERT INTO StudentHistoryChange (student_id, school_id, change_type, change_method, change_date,
                 change_detail, previous_value, new_value, remark) VALUES (?,?,?,?,?,?,?,?,?)'''
        cur.execute(sql1, (student_id, data['school_id'], change_type, change_method, change_date,
                           change_detail, previous_value, new_value, remark))
        cur.connection.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '更新学生信息成功',
            'data': {
                'student_id': student_id
            }
        })
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': '服务器内部错误',
            'detail': f'错误详情: {str(e)}'
        })
    finally:
        if conn:
            conn.close()


# 更改学生状态
@students_blueprint.route('/status', methods=['PATCH'])
@jwt_required()
@exts.permission_required('students', 'A')
def change_status():
    data = request.json.get('data', None)
    if not data or not isinstance(data, list) or len(data) == 0:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '参数不完整, data 不能为空'
        })
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        results = []
        for item in data:
            id_ = item.get('id', None)
            student_id = item.get('student_id', None)
            status = item.get('status', None)
            if not student_id or not status:
                results.append({
                    'code': 400,
                    'status': 'error',
                    'message': '参数不完整, student_id, status 不能为空'
                })
                continue
            # 查询学生是否存在
            if id_:
                cur.execute('SELECT * FROM AllStudentRecord WHERE id = ? AND student_id = ?', (id_, student_id))
            else:
                cur.execute('SELECT * FROM CurrentStudent WHERE student_id = ?', (student_id,))
            student = cur.fetchone()
            if not student:
                results.append({
                    "student_id": student_id,
                    "status": "error",
                    "message": "学生不存在"
                })
                continue
            # 更新学生状态
            if id_:
                sql = 'UPDATE AllStudentRecord SET status = ? WHERE id = ? AND student_id = ?'
                cur.execute(sql, (status, id_, student_id))
            else:
                sql = 'UPDATE CurrentStudent SET status = ? WHERE student_id = ?'
                cur.execute(sql, (status, student_id))
            # 新增学生历史变动信息
            change_type = '状态变动'
            change_method = '手动变更'
            change_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            remark = '页面手动更改学生状态'
            previous_value = {'status': student['status']}
            new_value = {'status': status}
            change_detail = f"""{change_type}, 字段变动 'status' : '{student['status']}' -> '{status}'; """
            previous_value = str(previous_value)
            new_value = str(new_value)
            sql1 = 'INSERT INTO StudentHistoryChange (student_id, school_id, change_type, change_method, change_date, ' \
                   'change_detail, previous_value, new_value, remark) VALUES (?,?,?,?,?,?,?,?,?)'
            cur.execute(sql1, (student_id, student['school_id'], change_type, change_method, change_date,
                               change_detail, previous_value, new_value, remark))
            results.append({
                "student_id": student_id,
                "status": "success",
                "message": "更改学生状态成功",
                "detail": f"学生状态更改为 {status}"
            })
        cur.connection.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '操作成功',
            'data': results
        })
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': '服务器内部错误',
            'detail': f'错误详情: {str(e)}'
        })
    finally:
        if conn:
            conn.close()


# 学生查询
@students_blueprint.route('/query', methods=['GET'])
@jwt_required()
@exts.permission_required('students', 'R')
def query_student():
    school_id = request.args.get('school_id', get_jwt()['school_id'], type=int)
    student_id = request.args.get('student_id', None, type=int)
    student_name = request.args.get('student_name', None, type=str)
    student_grade = request.args.get('student_grade', None, type=str)
    grade = request.args.get('grade', None, type=str)
    class_ = request.args.get('class', None, type=str)
    subject_selection = request.args.get('subject_selection', None, type=str)
    year = request.args.get('year', None, type=str)
    arrival_classtime = request.args.get('arrival_classtime', None, type=str)
    enrollment_date = request.args.get('enrollment_date', None, type=str)
    status = request.args.get('status', None, type=str)
    page = request.args.get('page', 1, type=int)
    page_size = request.args.get('page_size', 10, type=int)

    if page < 1 or page_size < 1:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "page 和 page_size 必须为正整数"
        }), 400

    offset = (page - 1) * page_size

    conditions_current = []
    conditions_history = []

    if school_id:
        conditions_current.append(f"cs.school_id = {school_id}")
        conditions_history.append(f"asr.school_id = {school_id}")
    if student_id:
        conditions_current.append(f"cs.student_id = {student_id}")
        conditions_history.append(f"asr.student_id = {student_id}")
    if student_name:
        conditions_current.append(f"cs.student_name LIKE '%{student_name}%'")
        conditions_history.append(f"asr.student_name LIKE '%{student_name}%'")
    if student_grade:
        conditions_current.append(f"cs.student_grade LIKE '%{student_grade}%'")
        conditions_history.append(f"asr.student_grade LIKE '%{student_grade}%'")
    if grade:
        conditions_current.append(f"cs.grade LIKE '%{grade}%'")
        conditions_history.append(f"asr.grade LIKE '%{grade}%'")
    if class_:
        conditions_current.append(f"cs.class = '{class_}'")
        conditions_history.append(f"asr.class = '{class_}'")
    if subject_selection:
        conditions_current.append(f"cs.subject_selection LIKE '%{subject_selection}%'")
        conditions_history.append(f"asr.subject_selection LIKE '%{subject_selection}%'")
    if year:
        conditions_current.append(f"cs.year LIKE '%{year}%'")
        conditions_history.append(f"asr.year LIKE '%{year}%'")
    if arrival_classtime:
        conditions_current.append(f"cs.arrival_classtime LIKE '%{arrival_classtime}%'")
        conditions_history.append(f"asr.arrival_classtime LIKE '%{arrival_classtime}%'")
    if enrollment_date:
        conditions_current.append(f"cs.enrollment_date LIKE '%{enrollment_date}%'")
        conditions_history.append(f"asr.enrollment_date LIKE '%{enrollment_date}%'")
    if status:
        conditions_current.append(f"cs.status = '{status}'")
        conditions_history.append(f"asr.status = '{status}'")

    where_clause_current = " AND ".join(conditions_current) if conditions_current else "1=1"
    where_clause_history = " AND ".join(conditions_history) if conditions_history else "1=1"

    sql = f"""
        SELECT * FROM (
            SELECT DISTINCT
                NULL AS id,
                cs.student_id, cs.student_name, cs.school_id, cs.student_grade,
                cs.grade, cs.class, cs.year, cs.subject_selection, cs.enrollment_date,
                cs.arrival_classtime, cs.status, cs.father_name, cs.father_phone,
                cs.mother_name, cs.mother_phone, cs.address, sc.school_name
            FROM
                CurrentStudent cs
            LEFT JOIN
                School sc ON cs.school_id = sc.school_id
            WHERE
                {where_clause_current}
            UNION ALL
            SELECT DISTINCT
                asr.id, asr.student_id, asr.student_name, asr.school_id, asr.student_grade,
                asr.grade, asr.class, asr.year, asr.subject_selection, asr.enrollment_date,
                asr.arrival_classtime, asr.status, asr.father_name, asr.father_phone,
                asr.mother_name, asr.mother_phone, asr.address, sc.school_name
            FROM
                AllStudentRecord asr
            LEFT JOIN
                School sc ON asr.school_id = sc.school_id
            WHERE
                {where_clause_history}
        ) AS combined_results
        ORDER BY student_id DESC
        LIMIT {page_size} OFFSET {offset}
    """

    sql_count = f"""
        SELECT COUNT(*) AS total FROM (
            SELECT DISTINCT NULL AS id, cs.student_id
            FROM CurrentStudent cs
            LEFT JOIN School sc ON cs.school_id = sc.school_id
            WHERE {where_clause_current}
            UNION ALL
            SELECT DISTINCT asr.id, asr.student_id
            FROM AllStudentRecord asr
            LEFT JOIN School sc ON asr.school_id = sc.school_id
            WHERE {where_clause_history}
        ) AS subquery
    """
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        # 查询总记录数
        cur.execute(sql_count)
        total_records = cur.fetchone()[0]
        total_pages = (total_records + page_size - 1) // page_size
        if 0 < total_pages < page:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "请求的页码超出范围",
                "details": f"请求的页码超出了最大页数（{total_pages}页）"
            }), 404
        # 查询当前页的数据
        cur.execute(sql)
        results = cur.fetchall()
        if not results:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到符合条件的学生成绩"
            }), 404
        results = [dict(zip([column[0] for column in cur.description], row)) for row in results]
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "查询成功",
            "data": {
                "total_records": total_records,
                "total_pages": total_pages,
                "current_page": page,
                "page_size": len(results),
                "results": results
            }
        })
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情: {str(e)}"
        }), 500
    finally:
        if conn:
            conn.close()


# 获取分析数据
@students_blueprint.route('/analysis', methods=['GET'])
@jwt_required()
@exts.permission_required('students', 'R')
def get_analysis_data():
    student_id = request.args.get('student_id', None, type=int)
    if not student_id:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '参数不完整, student_id 不能为空'
        })
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        # 查询学生是否存在
        cur.execute('SELECT * FROM CurrentStudent WHERE student_id = ?', (student_id,))
        student = cur.fetchone()
        student = dict(zip([column[0] for column in cur.description], student))
        if not student:
            return jsonify({
                'code': 400,
                'status': 'error',
                'message': '学生不存在',
                'detail': '学号 %s 的学生不存在' % student_id
            })
        # 查询学生当前学期进线成绩
        sql = "SELECT * FROM CurrentStuAllExamAnalysis WHERE student_id = ?"
        cur.execute(sql, (student_id,))
        exam_analysis = cur.fetchone()
        exam_analysis = dict(zip([column[0] for column in cur.description], exam_analysis))
        subject = ["Sum", "Yuwen", "Shuxue", "Waiyu", "Wuli", "Huaxue", "Shengwu", "Zhengzhi", "Lishi", "Dili"]
        subject_name = ["总分", "语文", "数学", "外语", "物理", "化学", "生物", "政治", "历史", "地理"]
        subject_list = []
        admission_rate = []
        admission_times = []
        for sub in subject:
            if exam_analysis[sub] is not None:
                subject_list.append(subject_name[subject.index(sub)])
                admission_rate.append(round((exam_analysis[sub] / exam_analysis["AllExam"])*100, 2))
                admission_times.append(exam_analysis[sub])
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '查询成功',
            'data': {
                'student_info': student,
                'exam_analysis': {
                    'all_exam': exam_analysis['AllExam'],
                    'subject_list': subject_list,
                    'admission_rate': admission_rate,
                    'admission_times': admission_times
                }
            }
        })
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': '服务器内部错误',
            'detail': f'错误详情: {str(e)}'
        })
    finally:
        if conn:
            conn.close()


# 学生数据迁移
@students_blueprint.route('/migrate', methods=['POST'])
@jwt_required()
@exts.permission_required('students', 'M')
def migrate_student():
    data = request.json.get('data', None)
    if not data or not isinstance(data, list) or len(data) == 0:
        return jsonify({
            'code': 400,
            'status': 'error',
            'message': '参数不完整, data 不能为空'
        })
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        results = []
        for item in data:
            student_grade = item.get('student_grade', None)
            arrival_classtime = item.get('arrival_classtime', None)
            # 查询该年级学生是否存在
            cur.execute('SELECT * FROM CurrentStudent WHERE student_grade = ?', (student_grade,))
            student = cur.fetchone()
            if not student:
                results.append({
                    'student_grade': student_grade,
                    'status': 'error',
                    'message': '该年级学生不存在'
                })
                continue
            # 对该学生年级的到班时间进行更新
            sql = 'UPDATE CurrentStudent SET arrival_classtime = ? WHERE student_grade = ?'
            cur.execute(sql, (arrival_classtime, student_grade))
            # 对数据进行迁移
            sql1 = """
                INSERT INTO AllStudentRecord (student_id, student_name, school_id, student_grade, grade, class, 
                subject_selection, year, arrival_classtime, father_name, father_phone, mother_name, mother_phone, 
                address, enrollment_date, status)
                SELECT student_id, student_name, school_id, student_grade, grade, class, subject_selection, year, 
                arrival_classtime, father_name, father_phone, mother_name, mother_phone, address, enrollment_date, 
                status FROM CurrentStudent WHERE student_grade = ?
            """
            cur.execute(sql1, (student_grade,))
            # 删除当前学生表中的数据
            sql2 = 'DELETE FROM CurrentStudent WHERE student_grade = ?'
            cur.execute(sql2, (student_grade,))
            results.append({
                'student_grade': student_grade,
                'status': 'success',
                'message': '数据迁移成功'
            })
        cur.connection.commit()
        return jsonify({
            'code': 200,
            'status': 'success',
            'message': '操作成功',
            'data': results
        })
    except Exception as e:
        return jsonify({
            'code': 500,
            'status': 'error',
            'message': '服务器内部错误',
            'detail': f'错误详情: {str(e)}'
        })
    finally:
        if conn:
            conn.close()

